import java.sql.*;

import javax.sql.*;

import org.apache.commons.lang.StringEscapeUtils;

import java.io.IOException;

import java.lang.*;
import java.util.*;
import java.util.Date;

public class DB 
{
	private String url = "jdbc:mysql://localhost:3306/";
	//private String url = "jdbc:mysql://warburg.cs.ualberta.ca:3306/";
	private String dbName = "USTC_Hucat_DB";
	private String driverName = "com.mysql.jdbc.Driver";
	private String userName = "whu4";
	private String passwd = "yz62658818";
	
	private HashMap<String, String> tableList = null;
	private HashMap<String, String> queryList = null;
	
	private Connection conn = null;
	private Statement st = null;
	
	DB()
	{
		tableList = new HashMap<String, String>();
		queryList = new HashMap<String, String>();
		
		//bug body
		tableList.put("bugBody", "(" +
				"bugid INT, " +
				"title VARCHAR(1000), " +
				"status VARCHAR(100), " +
				"owner VARCHAR(100), " +
				"closedOn TIMESTAMP, " +
				"type VARCHAR(100), " +
				"priority VARCHAR(100), " +
				"component VARCHAR(100), " +
				"stars INT, " +
				"reportedBy VARCHAR(100), " +
				"openedDate TIMESTAMP, " +
				//"description LONGTEXT, " +
				//"PRIMARY KEY(bugid), " +
				"UNIQUE(bugid)"
				+ ")");
		
		queryList.put("bugBody", 
				"INSERT into bugBody values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		
		
		//bug core
		tableList.put("description", "(" +
				"bugid INT, " +
				"description LONGTEXT" +
				")");
		
		queryList.put("description", "INSERT into description values(?, ?)");
		
		
		//comment
		tableList.put("comment", "(" +
				"bugid INT, " +
				"author VARCHAR(100), " +
				"whe TIMESTAMP, " +
				"wha LONGTEXT"
				//"PRIMARY KEY(bugid)"
				+ ")");
		
		queryList.put("comment", "INSERT into comment values(?, ?, ?, ?)");
		
		
		//change body
		tableList.put("changeBody", "(" +
				"project VARCHAR(100), " +
				"commitHash VARCHAR(1000), " +
				"treeHash VARCHAR(1000), " +
				"parentHashes VARCHAR(2000), " +
				"authorName VARCHAR(1000), " +
				"authorEmail VARCHAR(100), " +
				"authorDate TIMESTAMP, " +
				"commiterName VARCHAR(1000), " +
				"commiterEmail VARCHAR(100), " +
				"committerDate TIMESTAMP, " +
				"subject LONGTEXT"
				+ ")");
		
		queryList.put("changeBody", 
				"INSERT into changeBody values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		
		//change body
		tableList.put("advChangeBody", "(" +
				"project VARCHAR(100), " +
				"commitHash VARCHAR(1000), " +
				"committerDate TIMESTAMP, " +
				"line VARCHAR(1000)"
				+ ")");
		
		queryList.put("advChangeBody", 
				"INSERT into advChangeBody values(?, ?, ?, ?)");
		
		//message
		tableList.put("message", "(" +
				"commitHash VARCHAR(1000), " +
				"line LONGTEXT"
				+ ")");
		
		queryList.put("message", 
				"INSERT into message values(?, ?)");
		
		//target
		tableList.put("target", "(" +
				"commitHash VARCHAR(1000), " +
				"line LONGTEXT"
				+ ")");
		
		queryList.put("target", 
				"INSERT into target values(?, ?)");
		
		//filename
		tableList.put("filename", "(filename VARCHAR(100), UNIQUE(filename))");
		
		queryList.put("filename", 
				"INSERT ignore into filename values(?)");
		
		//file
		tableList.put("file", "(filename VARCHAR(1000), date Timestamp, label Int)");
		
		queryList.put("file", "INSERT into file values(?, ?, ?)");
	}
	
	DB(HashMap<String, String> tableList)
	{
		tableList = tableList;
	}
	
	//open connection
	public void connect() throws ClassNotFoundException, IllegalAccessException, 
		SQLException 
	{
		try
		{
			Class.forName(driverName).newInstance();
			conn = DriverManager.getConnection(url + dbName, userName, passwd);
			System.out.println("Connected to the database");
			
			st = conn.createStatement();
						
			//st.executeUpdate("DROP DATABASE if EXISTS " + dbName);
			//st.executeUpdate("CREATE DATABASE " + dbName);
			//st.executeUpdate("USE " + dbName);
			
			//speak utf-8
			st.executeUpdate("set names utf8");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	//close connection
	public void close() throws SQLException
	{
		try
		{
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Disconnected from database");
	}
	
	//build the table given the name and the schema
	public void buildTable(String tableName) throws Exception
	{
		String tableSchema = tableList.get(tableName);
		
		try
		{	
			st.executeUpdate("CREATE TABLE if NOT EXISTS " + tableName + tableSchema);
			//System.out.println("CREATE TABLE if NOT EXISTS " + tableName + tableSchema);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			
			System.err.println(tableName + tableSchema);
		}
		
		//System.out.println("Build table --> " + tableName);
	}
	
	//build all the table
	public void buildAll() throws Exception
	{
		Set<String> tableNameSet = tableList.keySet();
		
		for (String tableName: tableNameSet)
		{
			dropTable(tableName);
			
			String tableSchema = tableList.get(tableName);
			
			buildTable(tableName);
		}
	}
	
	//drop table gieven the name
	public void dropTable(String tableName)
	{
		try
		{
			st.executeUpdate("DROP TABLE if EXISTS " + tableName);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Drop table --> " + tableName);
	}
	
	//drop all the table
	public void dropAll() throws Exception
	{
		Set<String> tableNameSet = tableList.keySet();
		
		for (String tableName: tableNameSet)
		{
			dropTable(tableName);
			
			String tableSchema = tableList.get(tableName);
			
			dropTable(tableName);
		}
	}
	
	public PreparedStatement getPreparedStatement(String tableName) 
			throws SQLException
	{
		conn.setAutoCommit(false);
		
		PreparedStatement ps = null;
		
		try
		{
			String statement = queryList.get(tableName);
			
			ps = conn.prepareStatement(statement);
		}
		catch(Exception e)
		{
			e.printStackTrace();			
		}
		
		return ps;
	}
	
	public PreparedStatement getStreamPreparedStatement(String sqlStatement, 
			int rowNum) throws SQLException
	{
		//System.out.println(rowNum);
		
		PreparedStatement ps = null;
		
		try
		{	
			
			//make this preparedstatment return result stream instread of
			//returning all the results one at a time
			ps = conn.prepareStatement(sqlStatement, ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			
			//number of rows fetched each time
			ps.setFetchSize(rowNum);
			//ps.setFetchSize(Integer.MIN_VALUE);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return ps;
	}
	
	public PreparedStatement getDynamicCheckPreparedStatement(String sqlStatement) 
			throws SQLException
	{
		//conn.setAutoCommit(false);
		
		PreparedStatement ps = null;
		
		try
		{	
			ps = conn.prepareStatement(sqlStatement);
		}
		catch(Exception e)
		{
			e.printStackTrace();			
		}
		
		return ps;
	}
	
	
	//deprecated
	public void insertTable(String tableName, String insertStatement) 
			throws SQLException
	{
		String statement = new String("INSERT into ");
		statement += tableName;
		statement += " ";
		
		statement += insertStatement;
		
		//statement = statement.replace('\'', '1');
		
		//System.out.println(statement);
		
		try
		{
			st.executeUpdate(statement);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("hucat");
			System.out.println(statement);
		}
	}
	
	private void test() throws Exception
	{
		DB db = new DB();
		
		db.connect();
		db.buildAll();
		
		db.getPreparedStatement(new String("bugBody"));
		System.out.println("get the prepared statment for bugBody");
		
		db.getPreparedStatement(new String("description"));
		System.out.println("get the prepared statment for bugCore");
		
		db.getPreparedStatement(new String("comment"));
		System.out.println("get the prepared statment for comment");
		
		//db.getPreparedStatement(new String("commentCore"));
		//System.out.println("get the prepared statment for commentCore");
		
		db.dropAll();
		db.close();
	}
	
	static public void main(String argx[]) throws Exception
	{	
		
		DB db = new DB();
		
		db.connect();

		String sqlStatement = "SELECT authorName FROM changeBody WHERE commitHash = ?";
		PreparedStatement prest = db.conn.prepareStatement(sqlStatement);
		
		String commitHash = "fe365b416cc2f9ef7327f35c2b6360b0696dee83";
		
		prest.setString(1,commitHash);
		
		ResultSet rs = prest.executeQuery();
		
		while (rs.next())
		{
			  String authorName = rs.getString(1);
			  
			  System.out.println("actual sql author name -->" + authorName);
			  
			  System.out.println("unescaped author name --> " + StringEscapeUtils.unescapeHtml(authorName));
		
			  System.out.println("final author name --> " + StringEscapeUtils.unescapeXml(StringEscapeUtils.unescapeHtml(authorName)));
		}

		db.close();
		
	}

}
